﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Data;

namespace backend_wcf
{

    public class Database
    {
        MySqlConnection connection;

        public Database()
        {
            string MyConString = "SERVER=mazari.myvnc.com;" +
                    "DATABASE=moddb;" +
                    "UID=kth;" +
                    "PASSWORD=kth;";
            connection = new MySqlConnection(MyConString);
        }

        /// <summary>
        /// Method to test getting data from DB
        /// to be removed before release
        /// </summary>
        /// <returns></returns>
        public DataSet TestDB()
        {
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM krihog";
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            connection.Close();
            return dataSet;
        }

        /// <summary>
        /// Inserts a new row in the specified table with the specified values
        /// </summary>
        /// <param name="tableName">The table to insert into</param>
        /// <param name="values">A dictionarty with column name and value pairs</param>
        public void addData(string tableName, Dictionary<string, Object> values) {
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
		    //try{
                string sql_keys = "";
                string sql_values = "";
			    foreach(String key in values.Keys){
                    sql_keys += key + ", ";
                    sql_values += "@" + key + ", ";
			    }
                sql_keys = sql_keys.Substring(0, sql_keys.Length - 2);
                sql_values = sql_values.Substring(0, sql_values.Length - 2);
                command.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})", tableName, sql_keys, sql_values);

                foreach (String key in values.Keys)
                {
                    command.Parameters.AddWithValue("@" + key, values[key]);
                }
                command.ExecuteNonQuery();
                /*}
                catch(Exception e ){
                    // TODO Handle Exception correct
                    Console.Error.WriteLine(e.Message);
                }
             
             
                 if (values[key].GetType() == typeof(System.Byte[]))
                        {
                            byte[] rawData = (byte[])values[key];
                            sql_values += string.Format("{0}, ", Convert.ToString(rawData));
                        }
                        else
                        {
                        
             
                 */
                connection.Close();
	    }

        /// <summary>
        /// Inserts a new column in a table in the Database.
        /// </summary>
        /// <param name="tableName">Table that the column will be inserted into</param>
        /// <param name="columnName">Name of the new column</param>
        /// <param name="dbType">The Type the column will be</param>
	    public void createColumn(string tableName, string columnName, string dbType) {
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
		    //try{
                command.CommandText = string.Format("ALTER TABLE {0} ADD {1} {2}", tableName, columnName, dbType);
                command.ExecuteNonQuery();
		   /* }
            catch (Exception e)
            {
                // TODO Handle Exception correct
                Console.Error.WriteLine(e.Message);
            }*/
            connection.Close();
	    }

        /// <summary>
        /// Creates a new Table in the Database
        /// </summary>
        /// <param name="name">Name on the new Table</param>
	    public void createTable(string name) {
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
		    //try {
			    command.CommandText = string.Format("CREATE TABLE {0} (id int NOT NULL AUTO_INCREMENT, primary key(id))", name);
                command.ExecuteNonQuery();
		   /* }
            catch (Exception e)
            {
                // TODO Handle Exception correct
                Console.Error.WriteLine(e.Message);
            }*/
            connection.Close();
	    }

        /// <summary>
        /// Method for getting data from a speciefied Table in the Database.
        /// There is a logical expression parameter that can be ommited.
        /// </summary>
        /// <param name="tableName">which table to get data from</param>
        /// <param name="logic_expression">a WHERE clausule, can be NULL</param>
        /// <returns>A DataSet containing the result</returns>
        public DataSet getData(string tableName, string logic_expression)
        {
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            string sql = string.Format("SELECT * FROM {0}", tableName);
            if (logic_expression != null && logic_expression != string.Empty)
            {
                if(logic_expression.ToLower().Contains("where")){
                    sql += string.Format(" {0}", logic_expression);
                } else{
                    sql += string.Format(" WHERE {0}", logic_expression);
                }
            }

            command.CommandText = sql;
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, tableName);
            connection.Close();
            return dataSet;
        }

        public List<string> getStructure(string tableName)
        {
            List<string> list = new List<string>();
            connection.Open();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = string.Format("SHOW COLUMNS FROM {0}", tableName);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string tmp = string.Format("{0},{1}", reader["Field"], reader["Type"]);
                list.Add(tmp);
            }
            connection.Close();
            return list;
        }

        public List<string> getTables()
        {
            List<string> list = new List<string>();
            connection.Open();
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "SHOW TABLES";
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string tmp = reader["Tables_in_moddb"].ToString();
                list.Add(tmp);
            }
            connection.Close();
            return list;
        }

        public void deleteData(string tableName, int id)
        {
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            //try{
            command.CommandText = string.Format("DELETE FROM {0} WHERE id={1}", tableName, id);
            command.ExecuteNonQuery();
            /*}
            catch(Exception e ){
                // TODO Handle Exception correct
                Console.Error.WriteLine(e.Message);
            }*/
            connection.Close();
        }
    }
}